import pymysql
class DBUtil():
    __conn = None
    __cursor = None

    @classmethod
    def __get_conn(cls):
        if cls.__conn is None:
            cls.__conn = pymysql.connect(host="127.0.0.1",
                                         port=3306,
                                         user="admin",
                                         password="root",
                                         database="dataDB")
            return cls.__conn
    @classmethod
    def __get_cursor(cls):
        if cls.__cursor is None:
            cls.__cursor = cls.__get_conn().cursor()
        return cls.__cursor

    @classmethod
    def exe_sql(cls, sql):
        try:
            cursor = cls.__get_cursor()
            cursor.execute(sql)
            if sql.split()[0].lower() == "select":
                return cursor.fetchall()
            else:
                cls.__conn.commit()
                return cursor.rowcount
        except Exception as e:
            print(e)
            cls.__conn.rollback()
        finally:
            cls.__close_cursor()
            cls.__close_conn()

    @classmethod
    def __close_cursor(cls):
        if cls.__cursor:
            cls.__cursor.close()
            cls.__cursor = None

    @classmethod
    def __close_conn(cls):
        if cls.__conn:
            cls.__conn.close()
            cls.__conn = None

if __name__ == '__main__':
    create_class_sql = 'create table classes (\
                        id int unsigned auto_increment primary key not null,\
                        name varchar(30) not null);'
    # 创建classes表以及向表中插入数据
    DBUtil.exe_sql(create_class_sql)

    create_student_sql = "create table students(\
                                id int unsigned primary key auto_increment not null,\
                                deviced_id int \
                                gender varchar(14) default '',\
                                age tinyint unsigned default 0,\
                                university varchar(32)\
                                province varchar(32)\
                            );"
    insert_student_sql = "insert into students values\
                            (1,2138,'male',21,'北京大学','BeiJing'),\
                            (2,3214,'male',null,'复旦大学','Shanghai'),\
                            (3,6543,'female',20,'北京大学','BeiJing'),\
                            (4,2315,'female',23,'浙江大学','ZheJiang'),\
                            (5,5432,'male',25,'山东大学','Shandong');"